import os
import pymysql
import re

from openpyxl import load_workbook

from fileutil import getCustomFiles

database = pymysql.connect(host="localhost", port=3306, user="root",
                           password="123456", db="coins",
                           charset="utf8")  # 链接数据库（主机，端口，用户，密码等）
cursor = database.cursor()  # 数据库游标
# 读取excel文件
workbook = load_workbook("/home/thseus/workspaces/python/外国硬币汇总.xlsx")


# 得到非空结果
def getNotNone(s_value, s_preValue):
    if s_value is None or s_value == 'None':
        return s_preValue
    else:
        return s_value


def set_coins(workbook, s_continent, sheetName):
    cursor.execute(
        "delete from tr_coins where s_country in (select s_country from tp_area where s_continent =%s)",
        [s_continent])
    query = '''insert into tr_coins(s_country,s_period,s_currency,s_units,
    s_number,s_material,s_years,i_size,s_Obv,s_Rev,s_Subject,s_Ruler,s_remark,i_sort)
    VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%c,%s,%s,%s)'''
    sheet = workbook[sheetName]
    s_precountry = None
    s_preperiod = None
    s_precurrency = None
    periodDict = dict()
    i_sort = 1
    for i in range(2, sheet.max_row):
        # 国名
        s_country = str(sheet.cell(i, 2).value)
        s_country = getNotNone(s_country, s_precountry)
        if s_country != s_precountry:
            periodDict = get_period(s_country, 1)
        # 时期
        s_period = str(sheet.cell(i, 5).value)
        s_period = getNotNone(s_period, s_preperiod)
        if s_period != s_preperiod:
            i_sort = 1
        s_periodnum = periodDict.get(s_period)
        # print(s_period+"@"+s_periodnum)
        # 货币
        s_currency = str(sheet.cell(i, 6).value)
        s_currency = getNotNone(s_currency, s_precurrency).title()

        # 单位
        s_units = sheet.cell(i, 7).value

        if s_units is not None:
            s_unit = re.sub("[\u4E00-\u9FA5A-Za-z_]+", "",
                            sheet.cell(i, 7).value)
            # print(s_unit)
            if s_unit != "":
                s_units = str(s_unit)
            else:
                s_units = '0'
        else:
            s_units = '0'
        # 编号
        s_number = str(sheet.cell(i, 8).value)
        # print(s_number)
        # 材质
        s_material = str(sheet.cell(i, 9).value)
        # 年代
        s_years = str(sheet.cell(i, 10).value)
        # 尺寸
        size = sheet.cell(i, 11).value if sheet.cell(i, 11).value else 0.0
        i_size = float(size) if size != "" else 0.0
        s_Obv = str(sheet.cell(i, 12).value)
        s_Rev = str(sheet.cell(i, 13).value)
        s_Ruler = str(sheet.cell(i, 14).value)
        s_remark = str(sheet.cell(i, 16).value)
        # print(s_remark)
        if s_remark != 'None':
            s_Subject = 1
        else:
            s_Subject = 0
        values = (
            s_country,
            s_periodnum,
            s_currency,
            s_units,
            s_number,
            s_material,
            s_years,
            i_size,
            s_Obv,
            s_Rev,
            s_Subject,
            s_Ruler,
            s_remark,
            i_sort)
        cursor.execute(query, values)
        s_precountry = getNotNone(s_country, s_precountry)
        s_preperiod = getNotNone(s_period, s_preperiod)
        s_precurrency = getNotNone(s_currency, s_precurrency)
        i_sort += 3


def update_coins(s_countrypath,picpath, s_continent):
    """
    更新图片
    :param s_countrypath: 文件路径
    :param s_continent: 洲名
    :param sheetName:
    :return:
    """
    cursor.execute(
        "select a.s_country,s_period,s_currency,s_units,s_number,s_years from tr_coins a ,tp_area b where a.s_country = b.s_country and s_continent = %s and s_years <> 'None'",
        s_continent)
    filterNames = getCustomFiles(s_countrypath,pathName = picpath)
    # netherlands/nlg_1956_25c_a.jpg
    # netherlands/nlg_1956_25c_b.jpg

    results = cursor.fetchall()  # 获取查询的所有记录
    # 遍历tr_coinsb表结果
    for row in results:
        # 路径下所有图片
        for name in filterNames:
            # 路径和名称分离
            paths = os.path.split(name)
            s_country = row[0]
            if s_country.lower() == paths[0].lower():
                s_period = row[1]
                s_currency = row[2]
                s_units = row[3]
                s_number = row[4]
                # nlg_1961_2-5g_S_b.jpg nlg_1956_25c_a.jpg
                picName = paths[1]
                #分解上面名字
                names = picName.split("_")
                year = names[1]
                unit = names[2]
                years = row[5].split("~")
                # print(row[5]+"@"+str(len(years)))
                # 判断年度是～范围，还是单个年份
                if len(years) == 1 :
                    # 拼接单位 s_units+s_currency
                    s_unit = s_units + s_currency[0:1].lower()
                    if years.index(",") != -1:
                        yearnum = years.split(",")
                        for num in yearnum:
                            if unit == s_unit and row[5] == num:
                                if (names[-1] == "a.jpg"):
                                    cursor.execute(
                                        "update tr_coins set s_ObvImage = %s "
                                        "where s_country = %s and s_period = %s and "
                                        "s_currency = %s and s_units = %s and s_number = %s",
                                        ["/" + os.path.join(picpath, name), s_country,
                                         s_period, s_currency, s_units, s_number])
                                    print(["/" + os.path.join(picpath, name), s_country,
                                           s_period, s_currency, s_units, s_number])
                                else:
                                    cursor.execute(
                                        "update tr_coins set s_RevImage = %s "
                                        "where s_country = %s and s_period = %s and "
                                        "s_currency = %s and s_units = %s and s_number = %s",
                                        ["/" + os.path.join(picpath, name), s_country,
                                         s_period, s_currency, s_units, s_number])
                                    print(["/" + os.path.join(picpath, name), s_country,
                                           s_period, s_currency, s_units, s_number])
                    else:
                        if unit == s_unit and row[5] == year:
                            if(names[-1] == "a.jpg"):
                                cursor.execute(
                                    "update tr_coins set s_ObvImage = %s "
                                    "where s_country = %s and s_period = %s and "
                                    "s_currency = %s and s_units = %s and s_number = %s",
                                    ["/"+os.path.join(picpath,name),s_country,
                                     s_period,s_currency,s_units,s_number])
                                print(["/" + os.path.join(picpath, name), s_country,
                                       s_period, s_currency, s_units, s_number])
                            else:
                                cursor.execute(
                                    "update tr_coins set s_RevImage = %s "
                                    "where s_country = %s and s_period = %s and "
                                    "s_currency = %s and s_units = %s and s_number = %s",
                                    ["/" + os.path.join(picpath, name), s_country,
                                     s_period, s_currency, s_units, s_number])
                                print(["/" + os.path.join(picpath, name), s_country,
                                       s_period, s_currency, s_units, s_number])
                # 年份范围
                elif year>=years[0] and year<=years[1]:
                    # 拼接单位 s_units+s_currency首字母小写
                    s_unit = s_units + s_currency[0:1].lower()
                    if unit == s_unit:
                        if (names[-1] == "a.jpg"):
                            cursor.execute(
                                "update tr_coins set s_ObvImage = %s "
                                "where s_country = %s and s_period = %s and "
                                "s_currency = %s and s_units = %s and s_number = %s",
                                ["/" + os.path.join(picpath, name), s_country,
                                 s_period, s_currency, s_units, s_number])
                            print(["/" + os.path.join(picpath, name), s_country,
                                 s_period, s_currency, s_units, s_number])
                        else:
                            cursor.execute(
                                "update tr_coins set s_RevImage = %s "
                                "where s_country = %s and s_period = %s and "
                                "s_currency = %s and s_units = %s and s_number = %s",
                                ["/" + os.path.join(picpath, name), s_country,
                                 s_period, s_currency, s_units, s_number])
                            print(["/" + os.path.join(picpath, name), s_country,
                                   s_period, s_currency, s_units, s_number])


def set_area(workbook, s_continent, sheetName):
    sheet = workbook[sheetName]
    cursor.execute("delete from tp_area where s_continent = %s", [s_continent])
    query = '''insert into tp_area(s_continent,s_continentName,s_areaName,s_country)
        VALUES (%s,%s,%s,%s)'''
    s_preAreaName = None
    for i in range(2, sheet.max_row + 1):
        s_continentName = sheetName
        s_areaName = str(sheet.cell(i, 1).value)
        if s_preAreaName != s_areaName and s_areaName != 'None':
            s_preAreaName = s_areaName
        if s_preAreaName != s_areaName and s_areaName == 'None':
            s_areaName = s_preAreaName

        if sheet.cell(i, 2).value is not None:
            s_country = str(sheet.cell(i, 2).value)
            values = (s_continent, s_continentName, s_areaName, s_country)
            cursor.execute(query, values)


# type=0 key[s_value]=s_valuecmt type=1 key[s_valuecmt]=s_value
def get_enum(s_typecode, type=0):
    sql = 'select s_value,s_valuecmt from tp_enumvalue where s_typecode =%s'
    dicts = dict()
    try:
        cursor.execute(sql, [s_typecode])
        results = cursor.fetchall()  # 获取查询的所有记录
        # 遍历结果
        for row in results:
            if type == 0:
                dicts[row[0]] = row[1]
            else:
                dicts[row[1]] = row[0]
        return dicts
    except Exception as e:
        raise e


# type=0 key[s_country]=s_countryabb type=1 key[s_countryabb]=s_country
def get_country(type=0):
    sql = 'select s_country,s_countryabb from tp_country'
    dicts = dict()
    try:
        cursor.execute(sql)
        results = cursor.fetchall()  # 获取查询的所有记录
        # 遍历结果
        for row in results:
            if type == 0:
                dicts[row[0]] = row[1]
            else:
                dicts[row[1]] = row[0]
        return dicts
    except Exception as e:
        raise e


# type=0 key[s_country]=s_countryabb type=1 key[s_countryabb]=s_country
def get_period(s_country, type=0):
    sql = 'select s_period,s_periodName from tp_period where s_country = %s'
    dicts = dict()
    try:
        cursor.execute(sql, [s_country])
        results = cursor.fetchall()  # 获取查询的所有记录
        # 遍历结果
        for row in results:
            if type == 0:
                dicts[row[0]] = row[1]
            else:
                dicts[row[1]] = row[0]
        return dicts
    except Exception as e:
        raise e


def set_enum(workbook, sheetName):
    sheet = workbook[sheetName]
    cursor.execute("delete from tp_enumvalue where s_typecode = 'METALS'")
    query = '''insert into tp_enumvalue(s_typecode,s_value,s_typename,s_valuecmt,s_remark)
    VALUES (%s,%s,%s,%s,%s)'''
    for i in range(2, sheet.max_row + 1):
        s_typecode = str(sheet.cell(i, 1).value)
        s_value = str(sheet.cell(i, 2).value)
        s_typename = str(sheet.cell(i, 3).value)
        s_remark = str(sheet.cell(i, 4).value)
        s_valuecmt = str(sheet.cell(i, 5).value)
        values = (s_typecode, s_value, s_typename, s_valuecmt, s_remark)
        cursor.execute(query, values)


# 读取货币存入tp_currency
def set_currency(workbook, s_continent, sheetName):
    sheet = workbook[sheetName]
    cursor.execute("delete from tp_currency where s_continent =%s",
                   [s_continent])
    query = '''insert into tp_currency(s_country,s_currency,s_currencyName,s_continent)
        VALUES (%s,%s,%s,%s)'''
    currencyDict = dict()
    s_precountry = None
    for i in range(2, sheet.max_row + 1):
        s_country = str(sheet.cell(i, 2).value)
        s_country = getNotNone(s_country, s_precountry)
        s_currency = sheet.cell(i, 6).value
        s_units = sheet.cell(i, 7).value
        if s_units is not None and s_currency is not None:
            country_units = list()
            s_units = str(
                re.sub("(\-|\+)?\d+(\/\d+)?", "", sheet.cell(i, 7).value))
            country_units.append(s_country)
            country_units.append(s_units)
            currencyDict[s_currency.title()] = country_units
        s_precountry = getNotNone(s_country, s_precountry)

    for currency, country_units in currencyDict.items():
        s_country = country_units[0]
        s_currency = currency
        s_currencyName = country_units[1]
        s_continent = s_continent
        values = (s_country, s_currency, s_currencyName, s_continent)
        cursor.execute(query, values)


# 得到时期放入tp_period
def set_period(workbook, s_continent, sheetName):
    sheet = workbook[sheetName]
    cursor.execute(
        "delete from tp_period where s_continent =%s", [s_continent])
    query = '''insert into tp_period(s_country,s_period,s_periodName,s_continent)
    VALUES (%s,%s,%s,%s)'''
    num = 15
    s_precountry = None
    country_dict = get_country();
    print(country_dict)
    for i in range(2, sheet.max_row + 1):
        s_country = str(sheet.cell(i, 2).value)
        s_country = getNotNone(s_country, s_precountry)
        s_period = sheet.cell(i, 5).value
        if s_period is not None:
            if s_precountry != s_country:
                num = 15
            s_period1 = country_dict[s_country] + str(num)
            s_periodName = str(s_period)
            values = (s_country, s_period1, s_periodName, s_continent)
            cursor.execute(query, values)
            s_precountry = getNotNone(s_country, s_precountry)
            num += 2


def set_countrycode(workbook, sheetName):
    sheet = workbook[sheetName]
    cursor.execute("delete from tp_countrycode")
    query = '''insert into tp_countrycode(s_countrytwo,s_countryabb,i_code,s_iso,s_country,s_countryName,
    s_countryNameTw,s_countryNameHk,s_remark)
        VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)'''
    for i in range(2, sheet.max_row + 1):
        s_countrytwo = str(sheet.cell(i, 1).value)
        s_countryabb = str(sheet.cell(i, 2).value)
        i_code = int(sheet.cell(i, 3).value)
        s_iso = str(sheet.cell(i, 4).value)
        s_country = str(sheet.cell(i, 5).value)
        s_countryName = str(sheet.cell(i, 6).value)
        s_countryNameTw = str(sheet.cell(i, 7).value)
        s_countryNameHk = str(sheet.cell(i, 8).value)
        s_remark = str(sheet.cell(i, 9).value)
        values = (
            s_countrytwo,
            s_countryabb,
            i_code,
            s_iso,
            s_country,
            s_countryName,
            s_countryNameTw,
            s_countryNameHk,
            s_remark)
        cursor.execute(query, values)


def set_country(workbook, s_continent, sheetName):
    sheet = workbook[sheetName]
    cursor.execute(
        "delete from tp_country where s_country in (select s_country from tp_area where s_continent =%s)",
        [s_continent])
    query = '''insert into tp_country(s_country,s_countryName)
        VALUES (%s,%s)'''
    s_preAreaName = None
    for i in range(2, sheet.max_row + 1):
        s_continentName = sheetName
        s_country = str(sheet.cell(i, 2).value)
        s_countryName = str(sheet.cell(i, 3).value)
        if s_country != 'None' and s_countryName != 'None':
            values = (s_country, s_countryName)
            cursor.execute(query, values)
    cursor.execute(
        "update tp_country as a,tp_countrycode as b set a.s_countryabb = b.s_countryabb,"
        "a.s_countrytwo = b.s_countrytwo where a.s_country = b.s_country or a.s_countryName = b.s_countryName")


# set_enum(workbook, '参数')
# 地区
# set_area(workbook, 'Europe', '欧洲')
# set_area(workbook, 'Asia', '亚洲')
# set_area(workbook, 'Africa', '非洲')
# set_area(workbook, 'Americas', '美洲')
# set_area(workbook, 'Oceania', '大洋洲')
# 国家
# set_country(workbook, 'Europe', '欧洲')
# set_country(workbook,'Asia', '亚洲')
# set_country(workbook, 'Africa', '非洲')
# set_country(workbook, 'Americas', '美洲')
# set_country(workbook, 'Oceania', '大洋洲')
# 货币	单位
# set_currency(workbook, 'Europe', '欧洲')
# set_currency(workbook, 'Asia', '亚洲')
# set_currency(workbook, 'Africa', '非洲')
# set_currency(workbook, 'Americas', '美洲')
# set_currency(workbook, 'Oceania', '大洋洲')
# 时期
# set_period(workbook, 'Europe', '欧洲')
# set_period(workbook, 'Asia', '亚洲')
# set_period(workbook, 'Africa', '非洲')
# set_period(workbook, 'Americas', '美洲')
# set_period(workbook, 'Oceania', '大洋洲')

# 硬币
# set_coins(workbook, 'Europe','欧洲')
# set_coins(workbook, 'Asia', '亚洲')
# set_coins(workbook, 'Africa', '非洲')
# set_coins(workbook, 'Americas', '美洲')
# set_coins(workbook, 'Oceania', '大洋洲')

update_coins("/home/thseus/workspaces/flaskProject/mycoins/static/coin-eurpoe"
             "",'coin-eurpoe', 'Europe')
cursor.close()  # 关闭游标
database.commit()  # 提交操作
database.close()  # 关闭数据库
